/*
 * Copyright 2008 Carnegie Mellon University
 * Licensed under the Apache License, Version 2.0 (the "License"); 
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *  
 *   http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, 
 * software distributed under the License is distributed on an
 * "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
 * KIND, either express or implied. See the License for the
 * specific language governing permissions and limitations
 * under the License.
 */
package edu.cmu.lti.sepia.data.db;

import java.sql.SQLException;

import edu.cmu.lti.sepia.data.resource.ResourceManager;

/**
 * 
 * Responsible for storing SQL statements.
 * 
 * @author Hideki Shima
 *
 */
public class Query {
	
	/**
	 * Shared DB
	 */
	//LOG
	public static final String GET_LOGS = "SELECT * FROM @S@.log ORDER BY log_id DESC LIMIT 300";
	public static final String ADD_LOG = "INSERT INTO @S@.log VALUES (NULL,?,?,?,?,?,?)";
	
	//TASK
	public static final String GET_TASKS   = "SELECT * FROM @S@.task";
	public static final String ADD_TASK    = "INSERT INTO @S@.task VALUES (NULL,?,?,?,?,?,?,?,?,?,?,?,?)";
	public static final String UPDATE_TASK = "UPDATE @S@.task SET task_name=?, task_name_short=?, description=?, is_monolingual=?, src_lang=?, trg_lang=?, types=?, td_labels =?, ir_labels =?, segmenter_cmd=?, indri_path=?, current_phase=? WHERE task_id=?";
	public static final String DELETE_TASK = "DELETE FROM @S@.task WHERE task_name_short = ?";
	public static final String FIND_TASK_BY_ID = "SELECT * FROM @S@.task WHERE task_id = ?";
	public static final String FIND_TASK_BY_NAME = "SELECT * FROM @S@.task WHERE task_name_short = ?";
	public static final String FIND_LATEST_TASK = "SELECT * FROM @S@.task ORDER BY task_id DESC LIMIT 1";
	
	//USER
	public static final String GET_USERS   = "SELECT * FROM @S@.user";
	public static final String ADD_USER    = "INSERT INTO @S@.user VALUES (NULL,?,MD5(?),?,?,?)";
	public static final String UPDATE_USER = "UPDATE @S@.user SET user_name=?, password=MD5(?), role=?, display_lang=?, description=? WHERE user_id=?";
	public static final String UPDATE_USER2= "UPDATE @S@.user SET user_name=?, role=?, display_lang=?, description=? WHERE user_id=?";
	public static final String DELETE_USER = "DELETE FROM @S@.user WHERE user_id = ?";
	public static final String FIND_USER   = "SELECT * FROM @S@.user WHERE user_name = ?";
	public static final String FIND_LATEST_USER = "SELECT * FROM @S@.user ORDER BY user_id DESC LIMIT 1";
	public static final String UPDATE_DISPLAY_LANG = "UPDATE @S@.user SET display_lang=? WHERE user_id=?";
	
	//PERMISSION
	public static final String GET_PERMISSIONS       = "SELECT * FROM @S@.permission";
	public static final String FIND_PERMISSION_BY_U  = "SELECT * FROM @S@.permission WHERE user_id = ?";
	public static final String FIND_PERMISSION_BY_UT = "SELECT * FROM @S@.permission WHERE user_id = ? AND task_id = ?";
	public static final String UPDATE_PERMISSION     = "INSERT INTO @S@.permission (user_id, task_id, privilege) VALUES (?,?,?) ON DUPLICATE KEY UPDATE privilege=?";
	
	//FILE
	public static final String GET_FILES   = "SELECT * FROM @S@.uploaded_file";
  public static final String ADD_FILE    = "INSERT INTO @S@.uploaded_file VALUES (NULL,?,?,?,?,?,?,?,?)";
  public static final String UPDATE_FILE = "UPDATE @S@.uploaded_file SET user_id=?, task_id=?, date=?, description=?, size=? WHERE file_id=?";
  public static final String UPDATE_FILE_OWNER = "UPDATE @S@.uploaded_file SET user_id=? WHERE file_id=?";
  public static final String DELETE_FILE = "DELETE FROM @S@.uploaded_file WHERE file_id = ?";
  public static final String FIND_FILES_BY_USER = "SELECT * FROM @S@.uploaded_file WHERE user_id = ?";
  public static final String FIND_FILES_BY_USERTASK = "SELECT * FROM @S@.uploaded_file WHERE user_id = ? AND task_id = ?";
  public static final String FIND_FILE_BY_NAME = "SELECT * FROM @S@.uploaded_file WHERE type = ? AND file_name = ?";
  public static final String FIND_FILE = "SELECT * FROM @S@.uploaded_file WHERE file_id = ?";
  
  //FILE PERMISSION
  public static final String ADD_FILE_PERMISSION = "INSERT INTO @S@.file_permission VALUES (?,?)";
  public static final String DELETE_FILE_PERMISSION = "DELETE FROM @S@.file_permission WHERE file_id=?";
  public static final String FIND_FILE_PERMISSION_BY_FILE = "SELECT * FROM @S@.file_permission WHERE file_id=?";
  public static final String FIND_FILE_PERMISSION_BY_TASK = "SELECT * FROM @S@.file_permission WHERE task_id=?";
  public static final String FIND_FILE_PERMISSION_BY_FILETASK = "SELECT * FROM @S@.file_permission WHERE file_id=? AND task_id=?";
  
	/**
	 * Task specific DB
	 */
	//ANSWER
	public static final String INSERT_ANSWER  = "INSERT INTO @T@.answer VALUES (NULL,?,?,?,?,?,?,?)";
	public static final String DELETE_ANSWER  = "DELETE FROM @T@.answer WHERE answer_id = ?";
	public static final String DELETE_BYTOPIC = "DELETE FROM @T@.answer WHERE topic_id = ?";
	public static final String FIND_ANSWER    = "SELECT * FROM @T@.answer WHERE topic_id = ?";
	public static final String UPDATE_ANSWER_TEXT  = "UPDATE @T@.answer SET answer_sentence=? WHERE answer_id = ?";
	public static final String UPDATE_ANSWER_LABEL = "UPDATE @T@.answer SET label=? WHERE answer_id = ?";
	
	//NUGGET
	public static final String ADD_NUGGET     = "INSERT INTO @T@.nugget VALUES (NULL,?,?,?)";
	public static final String DELETE_NUGGET  = "DELETE FROM @T@.nugget WHERE nugget_id = ?";
	public static final String DELETE_NUGGET_BYTOPIC = "DELETE FROM @T@.nugget WHERE topic_id = ?";
	public static final String FIND_NUGGET    = "SELECT * FROM @T@.nugget WHERE topic_id = ?";
	
	//PROGRESS
	public static final String INSERT_PROGRESS  = "INSERT INTO @T@.progress (tool, topic_id, user_id, is_done) VALUES (?,?,?,?)";
	public static final String DELETE_PROGRESS_BYTOPIC = "DELETE FROM @T@.progress WHERE topic_id = ?";
	public static final String DELETE_PROGRESS1	= "DELETE FROM @T@.progress WHERE tool=? AND topic_id=?";
	public static final String DELETE_PROGRESS2	= "DELETE FROM @T@.progress WHERE tool=? AND topic_id=? AND user_id=?";
	public static final String LOAD_PROGRESS1   = "SELECT * FROM @T@.progress p WHERE tool=? AND topic_id=?";
	public static final String LOAD_PROGRESS2   = "SELECT * FROM @T@.progress p WHERE tool=? AND topic_id=? AND user_id=?";
	public static final String LOAD_PROGRESS3   = "SELECT * FROM @T@.progress p WHERE tool=?";
	public static final String LOAD_PROGRESS4   = "SELECT * FROM @T@.progress p WHERE tool=? AND user_id=?";
	
	//TOPIC
	public static final String GET_TOPICS    = "SELECT t.topic_id, t.external_id, t.answer_type, t.question_src, t.question_trg, t.narrative_src, t.narrative_trg, t.note, t.title FROM @T@.topic t"; //WHERE statement will be attached
	public static final String ADD_TOPIC     = "INSERT INTO @T@.topic VALUES (NULL,'','','','','','','',?)";
	public static final String ADD_TOPIC_FULL= "INSERT INTO @T@.topic VALUES (NULL,?,?,?,?,?,?,?,?)";
	public static final String DELETE_TOPIC  = "DELETE FROM @T@.topic WHERE topic_id = ?";
	public static final String FIND_TOPIC    = "SELECT * FROM @T@.topic WHERE topic_id=?";
	public static final String FIND_IID_FROM_EID = "SELECT topic_id FROM @T@.topic WHERE external_id=?";
	public static final String FIND_LATEST_TOPIC = "SELECT * FROM @T@.topic ORDER BY topic_id DESC LIMIT 1";
	public static final String UPDATE_TOPIC	 = "UPDATE @T@.topic SET external_id=?, answer_type=?, question_src=?, question_trg=?, narrative_src=?, narrative_trg=?, note=?, title=? WHERE topic_id=?";
	
	//VOTE
	public static final String LOAD_VOTES   = "SELECT v.nugget_id, v.user_id, v.is_voted " +
		"FROM @T@.vote v, @T@.nugget n WHERE n.nugget_id=v.nugget_id AND n.topic_id=?";
	public static final String UPDATE_VOTES = "INSERT INTO @T@.vote (nugget_id, user_id, is_voted) VALUES (?,?,?) ON DUPLICATE KEY UPDATE is_voted=?";

	//POOL
	public static final String GET_IR_POOLS   = "SELECT * FROM @T@.ir_pool";
	public static final String GET_QA_POOLS   = "SELECT * FROM @T@.qa_pool";
	public static final String ADD_IR_POOL    = "INSERT INTO @T@.ir_pool VALUES (NULL,?)";
	public static final String ADD_QA_POOL    = "INSERT INTO @T@.qa_pool VALUES (NULL,?)";
	public static final String DELETE_IR_POOL = "DELETE FROM @T@.ir_pool WHERE pool_id=?";
	public static final String DELETE_QA_POOL = "DELETE FROM @T@.qa_pool WHERE pool_id=?";
	public static final String FIND_LATEST_IR_POOL = "SELECT * FROM @T@.ir_pool ORDER BY pool_id DESC LIMIT 1";
  public static final String FIND_LATEST_QA_POOL = "SELECT * FROM @T@.qa_pool ORDER BY pool_id DESC LIMIT 1";
  
  //IR_JUDGMENT_UNIT
  public static final String ADD_IR_JUDGMENT_UNIT = "INSERT INTO @T@.ir_judgment_unit VALUES (NULL,?,?,?,?)";
  public static final String FIND_IR_JUDGMENT_UNIT_BY_TOPIC_AND_POOL = "SELECT * FROM @T@.ir_judgment_unit WHERE topic_id=? AND pool_id=?";
  public static final String FIND_IR_JUDGMENT_UNIT_BY_TOPIC_AND_DOCUMENT = "SELECT * FROM @T@.ir_judgment_unit WHERE topic_id=? AND document_id=?";
  public static final String FIND_IR_JUDGMENT_UNIT_BY_JUDGMENT_ID = "SELECT * FROM @T@.ir_judgment_unit WHERE judgment_id=?";
  public static final String DELETE_IR_JUDGMENT_UNIT_BY_POOL_ID = "DELETE FROM @T@.ir_judgment_unit WHERE pool_id=?";
  
  //IR JUDGMENT RESULT
  public static final String FIND_IR_JUDGMENT_RESULT_BY_ID = "SELECT * FROM @T@.ir_judgment_result WHERE judgment_id=?";
  public static final String UPDATE_IR_JUDGMENT_RESULT = "UPDATE @T@.ir_judgment_result SET judgment=?, evidence=?, time=? WHERE judgment_id=? AND user_id=?";
  public static final String INSERT_IR_JUDGMENT_RESULT = "INSERT INTO @T@.ir_judgment_result (judgment_id, user_id, judgment, evidence, time) VALUES (?,?,?,?,?)";
  public static final String GET_IR_JUDGMENT_RESULTS = "SELECT ju.topic_id, ju.document_id, jr.judgment, u.user_name, jr.time, jr.evidence FROM @T@.ir_judgment_result jr, @T@.ir_judgment_unit ju, @S@.user u WHERE jr.judgment_id=ju.judgment_id AND jr.user_id=u.user_id order by topic_id, document_id";
  
  //QA JUDGMENT UNIT
  public static final String ADD_QA_JUDGMENT_UNIT = "INSERT INTO @T@.qa_judgment_unit VALUES (NULL,?,?,?)";
  public static final String FIND_QA_JUDGMENT_UNIT_BY_TOPIC_AND_POOL = "SELECT * FROM @T@.qa_judgment_unit WHERE topic_id=? AND pool_id=?";
  public static final String FIND_QA_JUDGMENT_UNIT_BY_TOPIC = "SELECT * FROM @T@.qa_judgment_unit WHERE topic_id=?";
  public static final String DELETE_QA_JUDGMENT_UNIT_BY_POOL_ID = "DELETE FROM @T@.qa_judgment_unit WHERE pool_id=?";
  
  //QA JUDGMENT RESULT
  public static final String FIND_QA_JUDGMENT_RESULT = "SELECT * FROM @T@.qa_judgment_result WHERE judgment_id=?";
  public static final String UPDATE_QA_JUDGMENT_RESULT = "INSERT INTO @T@.qa_judgment_result (judgment_id, nugget_id, user_id, is_matched) VALUES (?,?,?,?) ON DUPLICATE KEY UPDATE is_matched=?";
  
	public static String generate( String sql, String table ) throws SQLException {
	  if (ResourceManager.p.getProperty("db_prefix")==null) {
	    new SQLException("db_prefix is not set in parameter");
	  }
		String taskDB   = ResourceManager.p.getProperty("db_prefix")+table;
		String sharedDB = ResourceManager.p.getProperty("db_prefix")+"shared";
		
		return sql.replaceAll("@T@", taskDB).replaceAll("@S@", sharedDB);	
	}
	
}
